import datetime

import pandas as pd
import os
from src.laptop.model.laptop_model_config import MODEL_DAYS
from src.laptop.preprocessor import prop_preprocessor as prop
from src.laptop.preprocessor.model_data_preprocessor import preprocess_model_data,preprocess_delete_column_to_train_data
from src.utils.config import logger
from src.utils.db_processor import presto_processor,mysql_prediction_processor
from src.laptop.model import laptop_model_config
from src.utils.util import get_today, check_date_str, format_date_string,check_conflict_file,check_dir
import pickle
from sklearn.preprocessing import OneHotEncoder


# opt使用 二级sku 二级等级，pop使用一级sku 一级等级
SETTLE_DATA5 = """
SELECT
cdp.document_item_id,
cast(psdp.settle_amount_num as int) AS item_quotation_price_num,
psdp.settle_document_create_date as settle_list_create_date,
cdp.create_date AS shop_out_date,
cast(dp.product_id AS int) as product_id ,
dp.product_name,
cast(dps.product_sku_id as int) AS product_sku_key,
pssp.property_name AS price_property_name_name,
pssp.property_value_name as price_property_value_name,
dp.product_brand_name,dp.product_brand_id,
cdp.product_combine_level_id AS level_id,
cdp.product_combine_level_name AS product_level_name,
cdp.product_no,psdp.quotation_document_no,

sku2level.secondary_level_id, --二级level,c2b使用
sku2level.secondary_sku_id, --二级skuid,c2b使用
CASE WHEN otpm.secondary_level_template_id IS NULL THEN -1 ELSE otpm.secondary_level_template_id END AS product_level_template_id, --二级等级模板,c2b使用
level.product_level_name as secondary_level_name --二级level_name,c2b使用
,0 is_pop
FROM dw.dw_centre_document_product cdp
JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no
JOIN dw.dw_platform_document_product pdp ON pdp.product_no = psdp.product_no AND pdp.quotation_document_no = psdp.quotation_document_no
JOIN dim.dim_product_sku dps ON dps.product_sku_id = cdp.product_combine_sku_id
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
--JOIN dim.dim_product_sku_sub_product pssp ON pssp.product_sku_id = dps.product_sku_id

join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on pdp.product_no =sku2level.product_no
JOIN ods.ods_opt_foundation_secondary_product_template_mapping otpm ON dp.product_id = otpm.product_id
inner join dim.dim_product_level level on level.product_level_id=sku2level.secondary_level_id
inner JOIN dim.dim_product_sku_sub_product pssp ON pssp.product_sku_id = sku2level.secondary_sku_id --二级sku

left join dw.dw_centre_document_product_out_info cdp_o on psdp.product_no = cdp_o.product_no AND psdp.settle_document_no = cdp_o.document_serial_no and cdp_o.document_create_date>=cast('{0}' as date) 
and cdp_o.document_create_date<=cast('{1}' as date)

WHERE dp.product_category_id = 5 AND cdp.document_category_id >= 200
AND cdp.document_item_status_id IN (201, 202, 204)
AND psdp.recycler_id NOT IN (12599, 507, 286)
AND pdp.quotation_document_type_id = 10

and (cdp_o.sale_out_cnt is null or cdp_o.sale_out_cnt=1)

and cdp.create_date not between  date'2023-01-17' and date'2023-01-25'
AND cdp.create_date >=  cast('{0}' as date) and cdp.create_date <=  cast('{1}' as date)

union all

SELECT
cdp.document_item_id,
cast(psdp.settle_amount_num as int) AS item_quotation_price_num,
psdp.settle_document_create_date as settle_list_create_date,
cdp.create_date AS shop_out_date,
cast(dp.product_id AS int) as product_id ,
dp.product_name,
cast(dps.product_sku_id as int) AS product_sku_key,
pssp.property_name AS price_property_name_name,
pssp.property_value_name as price_property_value_name,
dp.product_brand_name,dp.product_brand_id,
cdp.product_combine_level_id AS level_id,
cdp.product_combine_level_name AS product_level_name,
cdp.product_no,psdp.quotation_document_no,

cdp.product_combine_level_id as secondary_level_id, --二级level,c2b使用
dps.product_sku_id as secondary_sku_id, --二级skuid,c2b使用
CASE WHEN otpm.secondary_level_template_id IS NULL THEN -1 ELSE otpm.secondary_level_template_id END AS product_level_template_id, --二级等级模板,c2b使用
cdp.product_combine_level_name as secondary_level_name --二级level_name,c2b使用
,1 is_pop
FROM dw.dw_centre_document_product cdp
JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no
JOIN dw.dw_platform_document_product pdp ON pdp.product_no = psdp.product_no AND pdp.quotation_document_no = psdp.quotation_document_no
JOIN dim.dim_product_sku dps ON dps.product_sku_id = cdp.product_combine_sku_id
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
JOIN dim.dim_product_sku_sub_product pssp ON pssp.product_sku_id = dps.product_sku_id

JOIN ods.ods_opt_foundation_secondary_product_template_mapping otpm ON dp.product_id = otpm.product_id

left join dw.dw_centre_document_product_out_info cdp_o on psdp.product_no = cdp_o.product_no AND psdp.settle_document_no = cdp_o.document_serial_no and cdp_o.document_create_date>=cast('{0}' as date) 
and cdp_o.document_create_date<=cast('{1}' as date)

WHERE dp.product_category_id = 5 AND cdp.document_category_id >= 200
AND cdp.document_item_status_id IN (201, 202, 204)
AND psdp.recycler_id NOT IN (12599, 507, 286)
AND pdp.quotation_document_type_id = 10

and (cdp_o.sale_out_cnt is null or cdp_o.sale_out_cnt=1)
and cdp.create_date not between  date'2023-01-17' and date'2023-01-25'
AND cdp.create_date >=  cast('{0}' as date) and cdp.create_date <=  cast('{1}' as date)
and dps.product_sku_id not in
(
    select product_sku_id from algo.opt_c2b_laptop_sku_id
)
"""

drop_laptop_sku_id_table_sql = '''
drop table if exists algo.opt_c2b_laptop_sku_id
'''

opt_c2b_laptop_sku_id_sql = '''
    create table algo.opt_c2b_laptop_sku_id as
	SELECT
	distinct dps.product_sku_id
	FROM dw.dw_centre_document_product cdp
	JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no
	JOIN dw.dw_platform_document_product pdp ON pdp.product_no = psdp.product_no AND pdp.quotation_document_no = psdp.quotation_document_no
	JOIN dim.dim_product_sku dps ON dps.product_sku_id = cdp.product_combine_sku_id
	JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
	join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on pdp.product_no =sku2level.product_no
	JOIN ods.ods_opt_foundation_secondary_product_template_mapping otpm ON dp.product_id = otpm.product_id
	inner join dim.dim_product_level level on level.product_level_id=sku2level.secondary_level_id
	inner JOIN dim.dim_product_sku_sub_product pssp ON pssp.product_sku_id = sku2level.secondary_sku_id --二级sku
	left join dw.dw_centre_document_product_out_info cdp_o on psdp.product_no = cdp_o.product_no AND psdp.settle_document_no = cdp_o.document_serial_no and cdp_o.document_create_date>=cast('{0}' as date) 
	and cdp_o.document_create_date<=cast('{1}' as date)
	WHERE dp.product_category_id = 5 AND cdp.document_category_id >= 200
	AND cdp.document_item_status_id IN (201, 202, 204)
	AND psdp.recycler_id NOT IN (12599, 507, 286)
	AND pdp.quotation_document_type_id = 10
	and (cdp_o.sale_out_cnt is null or cdp_o.sale_out_cnt=1)
	and cdp.create_date not between  date'2023-01-17' and date'2023-01-25'
	AND cdp.create_date >=  cast('{0}' as date) and cdp.create_date <=  cast('{1}' as date)
'''


def load_settle_data(model_date=None):
    """
    读取结算数据
    :param model_date: 建模日期，如果为None，默认为当天
    :return:
    """
    if model_date is None:
        model_date = get_today()
    else:
        model_date = check_date_str(model_date)
    start_date = model_date - datetime.timedelta(days=MODEL_DAYS)
    d_start = datetime.datetime.strptime('2023-01-25', '%Y-%m-%d')
    if(start_date<d_start):
        diff=MODEL_DAYS+9
        start_date = model_date - datetime.timedelta(days=diff)
    logger.info('loading settle data from {}'.format(start_date))
    start_date = format_date_string(start_date)
    model_date = format_date_string(model_date)

    create_table_opt_c2b_laptop_sku_id_sql = opt_c2b_laptop_sku_id_sql.format(start_date, model_date)
    sql_info1 = presto_processor.load_sql(drop_laptop_sku_id_table_sql)
    sql_info2 = presto_processor.load_sql(create_table_opt_c2b_laptop_sku_id_sql)
    if sql_info2 is None:
        raise Exception("建表sql产生错误")

    sql = SETTLE_DATA5.format(start_date, model_date)
    data = presto_processor.load_sql(sql, parse_dates=['settle_list_create_date', 'shop_out_date'])
    # data['settle_list_create_date'] = data['settle_list_create_date'].apply(check_dated_str)
    # data['shop_out_date'] = data['shop_out_date'].apply(check_dated_str)
    return data

def strip_string(x):
    if type(x)==str:
        return x.strip()
    else:
        return x


def axit_transform(data):

    data['price_property_name_name'] = data['price_property_name_name'].fillna('unknown')
    data['price_property_value_name'] = data['price_property_value_name'].fillna('unknown')
    data['price_property_name_name'] = data['price_property_name_name'].map(lambda x: x.strip())
    data['price_property_value_name'] = data['price_property_value_name'].map(lambda x: x.strip())


    # data['price_property_name_name'] = data['price_property_name_name'].map(lambda x: strip_string(x))
    # data['price_property_value_name'] = data['price_property_value_name'].map(lambda x: strip_string(x))

    data['product_name'] = data['product_name'].map(lambda x: x.strip())
    data['product_brand_name'] = data['product_brand_name'].map(lambda x: x.strip())
    data['product_brand_id'] = data['product_brand_id'].astype(str)
    data['product_brand_id'] = data['product_brand_id'].map(lambda x: x.strip())
    data['product_brand_id'] = data['product_brand_id'].astype(int)
    # data.rename(columns={'product_brand_id': 'brand_id'}, inplace=True)
    data = data[data['price_property_name_name'].isin(prop.PROP_LIST)]

    # 将长数据转为宽数据
    pivot_data = data.pivot_table(index=['document_item_id'],
                                  columns='price_property_name_name',
                                  values='price_property_value_name', aggfunc=lambda x: x.unique())
    pivot_data = pivot_data.fillna('unknown')
    pivot_data = pivot_data.reset_index()

    data = data.drop(columns=['price_property_name_name', 'price_property_value_name']).drop_duplicates()
    data = pd.merge(data, pivot_data, on='document_item_id')
    data = preprocess_delete_column_to_train_data(data)
    return data


def preprocess_settle_data(data=None, model_date=None):
    """
    预处理结算数据
    :param data: 结算原始数据，如果为None，那么先读取结算数据
    :param model_date: 建模日期
    :return:
    """


    check_dir(laptop_model_config.MODEL_DIR)
    model_file = os.path.join(laptop_model_config.MODEL_DIR, laptop_model_config.MODEL_DATA)
    flag = 1
    if (flag == 1):
        if data is None:
            data = load_settle_data(model_date)

        check_conflict_file(laptop_model_config.MODEL_DIR,
                            laptop_model_config.MODEL_DATA,is_remove_old_file=True)
        ##存入pickle
        train_data = open(model_file, 'wb')
        pickle.dump(data, train_data)
        train_data.close()
    else:
        # 读取pickle
        train_data = open(model_file, 'rb')
        data = pickle.load(train_data)
    logger.info('loding data is end!')
    data = axit_transform(data)

    model_data = preprocess_model_data(data)
    product_ohe = OneHotEncoder(handle_unknown='ignore') #这儿的onehot模型只是为了查看型号是否为新型号
    product_ohe.fit(model_data[laptop_model_config.PRODUCT_FEATURES])

    return model_data,product_ohe

def load_model_product_config_item():
    """

    :param start_date: 起始日期
    :return:
    """

    logger.info('loading laptop_model_change_config...')
    sql = """
    SELECT product_id FROM laptop_model_change_config WHERE flag=1
    """
    return mysql_prediction_processor.load_sql(sql)